package youleche.com.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.LinkedList;
import java.util.List;

import youleche.com.connection.JdbcHelper;
import youleche.com.idao.IArea;
import youleche.com.model.MArea;

/**
 * 行政区域数据访问
 * @author Administrator
 *
 */
public class DArea implements IArea{

	/**
	 * 添加行政区域数据
	 * @param con 数据库连接
	 * @param area 区域数据
	 * @return 返回影响的行数
	 * @throws Exception 异常
	 */
	@Override
	public int addArea(Connection con, MArea area) throws Exception {
		PreparedStatement pre = null;
		try{
			String sql = "INSERT area(areaId,areaLevel,areaName,areaCode,A1,A2,A3,A4,A5,A6,A7,A8,A9,A10,parentCode) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);";
			pre = con.prepareStatement(sql);
			pre.setObject(1, area.getAreaId());
			pre.setObject(2, area.getAreaLevel());
			pre.setObject(3, area.getAreaName());
			pre.setObject(4, area.getAreaCode());
			pre.setObject(5, area.getA1());
			pre.setObject(6, area.getA2());
			pre.setObject(7, area.getA3());
			pre.setObject(8, area.getA4());
			pre.setObject(9, area.getA5());
			pre.setObject(10, area.getA6());
			pre.setObject(11, area.getA7());
			pre.setObject(12, area.getA8());
			pre.setObject(13, area.getA9());
			pre.setObject(14, area.getA10());
			pre.setObject(15, area.getParentCode());
	
			return pre.executeUpdate();
		}finally{
			JdbcHelper.free(null, pre);
		}
	}

	/**
	 * 通过代码判断是否存在区域数据
	 * @param con 数据库连接
	 * @param code 代码区域
	 * @return 返回是否存在 true 存在
	 * @throws Exception 异常
	 */
	@Override
	public boolean isExistAreaByCode(Connection con, String code)
			throws Exception {
		PreparedStatement ps = null;
		ResultSet rs = null;
		try{
		String sql = "select areaId from area where areaCode = '"+code+"' limit 1;";
		ps = con.prepareStatement(sql);
		rs = null;
		rs = ps.executeQuery();
		String areaId = "";
		while(rs.next()) {
			areaId = rs.getString(1);
			break;
		}
		
		if("".equals(areaId)){
			return false;
		}
		
		return true;
		}finally{
			JdbcHelper.free(rs, ps);
		}
	}

	/**
	 * 根据父亲code得到区域数据
	 * @param con 数据连接
	 * @param parentCode 父亲code
	 * @return 返回数据集合
	 * @throws Exception 异常
	 */
	@Override
	public List<MArea> getAreaByParentCode(Connection con, String parentCode)
			throws Exception {
		ResultSet rs = null;
		PreparedStatement ps = null;
		try{
			String sql = "select areaId,areaLevel,areaName,areaCode,A1,A2,A3,A4,A5,A6,A7,A8,A9,A10,parentCode from area where parentCode='" + parentCode + "' order by areaCode asc;";
			ps = con.prepareStatement(sql);
			
			rs = ps.executeQuery();
			List<MArea> list = null;
			MArea area = null;
			if(rs != null){
				list = new LinkedList<MArea>();
				while(rs.next()){
					area = new MArea();
					area.setAreaId(rs.getString(1));
					area.setAreaLevel(rs.getString(2));
					area.setAreaName(rs.getString(3));
					area.setAreaCode(rs.getString(4));
					area.setA1(rs.getString(5));
					area.setA2(rs.getString(6));
					area.setA3(rs.getString(7));
					area.setA4(rs.getString(8));
					area.setA5(rs.getString(9));
					area.setA6(rs.getString(10));
					area.setA7(rs.getString(11));
					area.setA8(rs.getString(12));
					area.setA9(rs.getString(13));
					area.setA10(rs.getString(14));
					area.setParentCode(rs.getString(15));
					list.add(area);
				}
			}
			
			return list;
		}finally{
			JdbcHelper.free(rs, ps);
		}
			
	}

	/**
	 * 根据城市和等级获取区域数据(直辖市)
	 * @param con 数据库连接
	 * @param areaName 区域名
	 * @return 直辖市区域数据
	 * @throws Exception
	 */
	@Override
	public List<MArea> getAreaByArealevelAndAreaNameForZXS(Connection con, String areaName) throws Exception {
		ResultSet rs = null;
		PreparedStatement ps = null;
		try{
			String sql = "select areaId,areaLevel,areaName,areaCode,A1,A2,A3,A4,A5,A6,A7,A8,A9,A10,parentCode from area where parentCode = (select areacode from area where parentCode = (select areacode from area where areaName like '"+areaName+"%' and areaLevel='1')  limit 1) order by areacode asc";
			ps = con.prepareStatement(sql);
			rs = ps.executeQuery(sql);
			List<MArea> list = null;
			MArea area = null;
			if(rs != null){
				list = new LinkedList<MArea>();
				while(rs.next()){
					area = new MArea();
					area.setAreaId(rs.getString(1));
					area.setAreaLevel(rs.getString(2));
					area.setAreaName(rs.getString(3));
					area.setAreaCode(rs.getString(4));
					area.setA1(rs.getString(5));
					area.setA2(rs.getString(6));
					area.setA3(rs.getString(7));
					area.setA4(rs.getString(8));
					area.setA5(rs.getString(9));
					area.setA6(rs.getString(10));
					area.setA7(rs.getString(11));
					area.setA8(rs.getString(12));
					area.setA9(rs.getString(13));
					area.setA10(rs.getString(14));
					area.setParentCode(rs.getString(15));
					list.add(area);
				}
			}
			
			return list;
		}finally{
			JdbcHelper.free(rs, ps);
		}
	}

	/**
	 * 根据区域名称得到区域
	 * @param con 数据库连接
	 * @param name 区域名称
	 * @return 返回区域数据
	 * @throws Exception 异常
	 */
	@Override
	public MArea getAreaByAreaName(Connection con, String name)
			throws Exception {
		ResultSet rs = null;
		PreparedStatement ps = null;
		try{
			String sql = "select areaId,areaLevel,areaName,areaCode,A1,A2,A3,A4,A5,A6,A7,A8,A9,A10,parentCode from area where areaName='" + name + "' limit 1;";
			ps = con.prepareStatement(sql);
			
			rs = ps.executeQuery();
			MArea area = null;
			if(rs != null){
				while(rs.next()){
					area = new MArea();
					area.setAreaId(rs.getString(1));
					area.setAreaLevel(rs.getString(2));
					area.setAreaName(rs.getString(3));
					area.setAreaCode(rs.getString(4));
					area.setA1(rs.getString(5));
					area.setA2(rs.getString(6));
					area.setA3(rs.getString(7));
					area.setA4(rs.getString(8));
					area.setA5(rs.getString(9));
					area.setA6(rs.getString(10));
					area.setA7(rs.getString(11));
					area.setA8(rs.getString(12));
					area.setA9(rs.getString(13));
					area.setA10(rs.getString(14));
					area.setParentCode(rs.getString(15));
				}
			}
			
			return area;
		}finally{
			JdbcHelper.free(rs, ps);
		}
	}
/**
	 * 获取中国行政区域数据最后一条记录（最新一条）code
	 * @param con 数据库连接
	 * @return 获取最后一条code
	 * @throws Exception 异常
	 */
	@Override
	public String getOnlyOneAreaByAddTimeDesc(Connection con) throws Exception {
		PreparedStatement ps = null;
		ResultSet rs = null;
		try{
			String sql = "select areaCode from area order by addTime desc";
			ps = con.prepareStatement(sql);
			rs = ps.executeQuery();
			while(rs.next()) {
				return rs.getString(1);
			}
			
			return "";
		}finally{
			JdbcHelper.free(rs, ps);
		}
	}

	/**
	 * 根据code得到区域数据
	 * @param con 数据连接
	 * @param code code
	 * @return 返回数据集合
	 * @throws Exception 异常
	 */
	@Override
	public MArea getAreaByAreaCode(Connection con, String code)
			throws Exception {
		ResultSet rs = null;
		PreparedStatement ps = null;
		try{
			String sql = "select areaId,areaLevel,areaName,areaCode,A1,A2,A3,A4,A5,A6,A7,A8,A9,A10,parentCode from area where areaCode='" + code + "';";
			ps = con.prepareStatement(sql);
			
			rs = ps.executeQuery();
			MArea area = null;
			if(rs != null){
				while(rs.next()){
					area = new MArea();
					area.setAreaId(rs.getString(1));
					area.setAreaLevel(rs.getString(2));
					area.setAreaName(rs.getString(3));
					area.setAreaCode(rs.getString(4));
					area.setA1(rs.getString(5));
					area.setA2(rs.getString(6));
					area.setA3(rs.getString(7));
					area.setA4(rs.getString(8));
					area.setA5(rs.getString(9));
					area.setA6(rs.getString(10));
					area.setA7(rs.getString(11));
					area.setA8(rs.getString(12));
					area.setA9(rs.getString(13));
					area.setA10(rs.getString(14));
					area.setParentCode(rs.getString(15));
				}
			}

			return area;
		}finally{
			JdbcHelper.free(rs, ps);
		}
	}
}
